using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.Collections;
using DTO;

namespace DAO
{
    public class TheLoaiDao
    {
        #region Methods
        public DataSet LayBang()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From THE_LOAI";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);   // can thiet
            da.Fill(ds);
            //B5: Dong ket noi CSDL
            cn.Close();
            return ds;
        }



        public IList LayDanhSach()
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From THE_LOAI";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            TheLoaiDto tl = null;
            ArrayList ds = new ArrayList();
            while (dr.Read())
            {
                tl = new TheLoaiDto();
                tl.MaTheLoai = (int)dr["MaTheLoai"];
                tl.TheLoai = (string)dr["TheLoai"];

                ds.Add(tl);
            }

            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return ds;
        }



        public void CapNhatBang(DataSet ds)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From THE_LOAI";
            //B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);  // khi cap nhat bang phai co dong lenh nay
            da.Update(ds.Tables[0]);
            //B5: Dong ket noi CSDL
            cn.Close();
        }



        public TheLoaiDto TimKiem(int maTheLoai)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Select * From THE_LOAI Where MaTheLoai = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer);
            cmd.Parameters[0].Value = maTheLoai;
            OleDbDataReader dr = cmd.ExecuteReader();
            TheLoaiDto tl = null;
            while (dr.Read())
            {
                tl = new TheLoaiDto();
                tl.MaTheLoai = (int)dr["MaTheLoai"];
                tl.TheLoai = (string)dr["TheLoai"];
            }

            //B5: Dong ket noi CSDL
            cn.Close();
            return tl;
        }



        public void Them(TheLoaiDto tl)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into THE_LOAI(TheLoai) values (?)";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TheLoai", OleDbType.WChar);

            cmd.Parameters["@TheLoai"].Value = tl.TheLoai;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            tl.MaTheLoai = (int)cmd.ExecuteScalar();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Xoa(int maTheLoai)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From THE_LOAI Where MaTheLoai = ? ";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer);
            cmd.Parameters["@MaTheLoai"].Value = maTheLoai;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }




        public void Sua(TheLoaiDto tl)
        {
            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = DataProvider.ConnectionData();
            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Update THE_LOAI set TheLoai = ? where MaTheLoai = ?";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TheLoai", OleDbType.WChar);
            cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer);
            cmd.Parameters["@MaTheLoai"].Value = tl.MaTheLoai;
            cmd.Parameters["@TheLoai"].Value = tl.TheLoai;
            cmd.ExecuteNonQuery();
            //B5: Dong ket noi CSDL
            cn.Close();
        }
        #endregion Methods

    }
}
